FireBird - jak zjistit zavislosti mezi tabulkami
Otázka od: Karel Rys
19. 8. 2004 8:32
Dobry den,
poradite prosim nekdo, jak se daji programove zjistit zavislosti mezi tabulkami
databaze (FireBird
1.5)? Propojeni pres foreign key. Potrebuji neco podobneho, jako umi IBPump -
odkudsi nacte
zavislosti a pri vytvareni kopie databaze postupuje ve spravnem poradi. Krom
zavislosti samotnych
bych potreboval
Mimochodem: je to jen muj dojem, ze IBConsole spatne zobrazuje cas, ktery
zabralo zpracovani
dotazu? Alespon pri testech proti FireBirdu se mi to tak jevi, ze misto napr.
1.8270 sekundy
zobrazi 1.0827 sekundy...
Diky za kazdy namet,
Karel Rys
Odpovedá: David Fajfr
19. 8. 2004 9:33
> poradite prosim nekdo, jak se daji programove zjistit zavislosti mezi
tabulkami databaze (FireBird
> 1.5)? Propojeni pres foreign key.
1) Objekty, na kterych tabulka zavisi (nejdriv musis prevest ty objekty a
pak tu tabulku):
select
A.RDB$RELATION_NAME as TABLE_NAME,
C.RDB$RELATION_NAME as FK_TABLE_NAME
from
RDB$RELATION_CONSTRAINTS A,
RDB$REF_CONSTRAINTS B,
RDB$RELATION_CONSTRAINTS C,
RDB$INDEX_SEGMENTS D,
RDB$INDEX_SEGMENTS E
where
(A.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY') and
(A.RDB$RELATION_NAME = :TABLE_NAME) and
(A.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME) and
(B.RDB$CONST_NAME_UQ=C.RDB$CONSTRAINT_NAME) and
(C.RDB$INDEX_NAME=D.RDB$INDEX_NAME) and
(A.RDB$INDEX_NAME=E.RDB$INDEX_NAME)
2) Opacne. Objekty, ktere zavisi na tabulce:
select
A.RDB$RELATION_NAME as DEPENDED_TABLE
from
RDB$REF_CONSTRAINTS B,
RDB$RELATION_CONSTRAINTS A,
RDB$RELATION_CONSTRAINTS C,
RDB$INDEX_SEGMENTS D,
RDB$INDEX_SEGMENTS E
where
(A.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY') and
(A.RDB$CONSTRAINT_NAME = B.RDB$CONSTRAINT_NAME) and
(B.RDB$CONST_NAME_UQ=C.RDB$CONSTRAINT_NAME) and
(C.RDB$INDEX_NAME=D.RDB$INDEX_NAME) and
(A.RDB$INDEX_NAME=E.RDB$INDEX_NAME) and
(C.RDB$RELATION_NAME = :TABLE_NAME)
group by
A.RDB$RELATION_NAME
David
Odpovedá: Pavel Cisar
19. 8. 2004 9:39
Haj hou!
On 19 Aug 2004 at 9:31, Karel Rys wrote:
> poradite prosim nekdo, jak se daji programove zjistit zavislosti mezi
> tabulkami databaze (FireBird 1.5)? Propojeni pres foreign key.
> Potrebuji neco podobneho, jako umi IBPump - odkudsi nacte zavislosti a
> pri vytvareni kopie databaze postupuje ve spravnem poradi. Krom
> zavislosti samotnych bych potreboval
Tato informace je ulozena v tabulce RDB$DEPENDENCIES pro vsechny db
objekty. Typ objektu je hodnota z tabulky RDB$TYPES.
Nasledujici prikazy / procedury jsou pro QLI, a vypisi zavislosti
objektu nebo na objektu (hodnota parametru je vyzadana interaktivne).
Jsou v GDML, ale urcite si je snadno prevedete na SQL 
QLI procedura show_dependant
print distinct rdb$depended_on_name of rdb$dependencies
report rdb$dependencies cross t in rdb$types with
rdb$dependent_type=t.rdb$type and t.rdb$field_name='RDB$OBJECT_TYPE'
and rdb$depended_on_name=*.'object name' sorted by
rdb$dependent_name
set columns = 100
at top of rdb$dependent_name print t.rdb$type_name using xxxxxxxxxx,
rdb$dependent_name
print rdb$dependencies.rdb$field_name
end_report
QLI procedura show_dependencies
print distinct rdb$dependent_name of rdb$dependencies
report rdb$dependencies cross t in rdb$types with
rdb$depended_on_type=t.rdb$type and
t.rdb$field_name='RDB$OBJECT_TYPE' and rdb$dependent_name=*.'object
name'
sorted by rdb$depended_on_name
set columns = 100
at top of rdb$depended_on_name print t.rdb$type_name using
xxxxxxxxxx, rdb$depended_on_name
print rdb$dependencies.rdb$field_name
end_report
S pozdravem
Pavel Cisar ( ICQ: 89017288)
Mobil: 724 281429
http://www.ibphoenix.cz
Vse co potrebujete pro Firebird a InterBase